﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DAL
{
    public class DAL_Cook
    {
        private const string usp_SelectCookTop      = "usp_SelectCookTop";
        private const string usp_SelectCook         = "usp_SelectCook";
        private const string usp_SelectCookByID     = "usp_SelectCookByID";
        private const string usp_SelectCookDetail   = "usp_SelectCookDetail";
        private const string usp_InsertCook         = "usp_InsertCook";
        private const string usp_UpdateCook         = "usp_UpdateCook";
        private const string usp_UpdateCookNotImage = "usp_UpdateCookNotImage";
        private const string usp_DeleteCook         = "usp_DeleteCook";

        private const Int32  OK                     = 1;
        private const Int32  NOT_OK                 = -1;

        public DataTable Get_CookTop()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectCookTop, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Cook_All()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectCook, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataTable Get_Cook_ByID(int p_cook_id)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectCookByID, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.BigInt;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Cook_id";
            sqlParameter[0].Value = p_cook_id;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }

        public DataSet Get_CookDetail(int p_cook_id)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectCookDetail, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;            

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.Int;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Cook_id";
            sqlParameter[0].Value = p_cook_id;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(ds);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return ds;
            }
            return ds;
        }

        public bool DeleteCook(int p_cook_id)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_DeleteCook, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.BigInt;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Cook_id";
            sqlParameter[0].Value = p_cook_id;
            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public Int32 InsertCook(Cook pCook)
        {
            Int32 _iCook_id = 0;

            SqlParameter[] sqlParameter = new SqlParameter[5];
            SqlCommand sqlCommand = new SqlCommand(usp_InsertCook, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.NVarChar;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Title";
            sqlParameter[0].Value = pCook.TITLE;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].SqlDbType = SqlDbType.NText;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@Content_desc";
            sqlParameter[1].Value = pCook.CONTENT_DESC;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].SqlDbType = SqlDbType.NText;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@Content_detail";
            sqlParameter[2].Value = pCook.CONTENT_DETAIL;            

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].SqlDbType = SqlDbType.Image;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@Image_data";
            sqlParameter[3].Value = pCook.IMAGE_DATA;

            sqlParameter[4] = new SqlParameter();
            sqlParameter[4].SqlDbType = SqlDbType.Int;
            sqlParameter[4].Direction = ParameterDirection.Output;
            sqlParameter[4].ParameterName = "@Cook_id";

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
                _iCook_id = Int32.Parse(sqlParameter[4].Value.ToString());
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return NOT_OK;
            }
            return _iCook_id;
        }

        public bool UpdateCook(Cook pCook)
        {
            SqlParameter[] sqlParameter = new SqlParameter[5];
            SqlCommand sqlCommand = new SqlCommand(usp_UpdateCook, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.NVarChar;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Title";
            sqlParameter[0].Value = pCook.TITLE;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].SqlDbType = SqlDbType.NText;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@Content_desc";
            sqlParameter[1].Value = pCook.CONTENT_DESC;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].SqlDbType = SqlDbType.NText;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@Content_detail";
            sqlParameter[2].Value = pCook.CONTENT_DETAIL;            

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].SqlDbType = SqlDbType.Image;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@Image_data";
            sqlParameter[3].Value = pCook.IMAGE_DATA;

            sqlParameter[4] = new SqlParameter();
            sqlParameter[4].SqlDbType = SqlDbType.Int;
            sqlParameter[4].Direction = ParameterDirection.Input;
            sqlParameter[4].ParameterName = "@Cook_id";
            sqlParameter[4].Value = pCook.COOK_ID;

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }

        public bool UpdateCookNotImage(Cook pCook)
        {
            SqlParameter[] sqlParameter = new SqlParameter[4];
            SqlCommand sqlCommand = new SqlCommand(usp_UpdateCookNotImage, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].SqlDbType = SqlDbType.NVarChar;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@Title";
            sqlParameter[0].Value = pCook.TITLE;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].SqlDbType = SqlDbType.NText;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@Content_desc";
            sqlParameter[1].Value = pCook.CONTENT_DESC;

            sqlParameter[2] = new SqlParameter();
            sqlParameter[2].SqlDbType = SqlDbType.NText;
            sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[2].ParameterName = "@Content_detail";
            sqlParameter[2].Value = pCook.CONTENT_DETAIL;            

            sqlParameter[3] = new SqlParameter();
            sqlParameter[3].SqlDbType = SqlDbType.Int;
            sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[3].ParameterName = "@Cook_id";
            sqlParameter[3].Value = pCook.COOK_ID;

            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }
    }
}
